package com.admin.service.borservice;

import com.admin.dao.bordao.BorDao;
import com.admin.dao.bordao.BorDaoImpl;
import com.borrower.dto.BorrowInfoBean;
import com.pojo.BorrowerBean;
import com.utils.DBUtil;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class BorServiceImpl implements BorService{

    private BorDao borDao = null;

    public BorServiceImpl() {
        borDao = new BorDaoImpl();
    }


    @Override
    public List<BorrowerBean> getBorInfo(BorrowerBean bor) {
        List<BorrowerBean> list = null;
        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "select * from borrower where username like '%" + bor.getUserName() + "%'";
            list = borDao.getBorInfo(sql, conn);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }


    @Override
    public boolean updateBorBalance(BorrowerBean bor) {
        boolean bool = false;
        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "UPDATE borrower SET balance = IFNULL(balance,0) + ? WHERE idCard = ? ;";
            bool = borDao.updateBorBalance(sql, conn, bor);
            if (bool) {
                conn.commit();
            } else {
                conn.rollback();
            }
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return bool;
    }

    @Override
    public List<BorrowerBean> getAllBorInfo() {
        List<BorrowerBean> list = null;
        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "select * from borrower";
            list = borDao.getAllBorInfo(sql, conn);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    @Override
    public List<BorrowerBean> getBorInfoByName(BorrowerBean bor) {
        List<BorrowerBean> list = null;
        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "select * from borrower where username = ?";
            list = borDao.getBorInfoByName(sql, conn, bor);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    @Override
    public boolean getIdCardBookNameAddFreeAndTime(String idCard, String bookName, double renewalFree, String renewalTime) {
        boolean bool=false;
        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "UPDATE borinfo set repaydate=?,price=price+? where borid=(SELECT borid from borrower where idcard=?) and bookid=(select bookid from books where bookname=?)";
            bool = borDao.getIdCardBookNameAddFreeAndTime(sql, conn, idCard,bookName,renewalFree,renewalTime);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return bool;
    }

    @Override
    public BorrowerBean getBorInfoByIdCard(BorrowerBean bor) {
        BorrowerBean getBor = null;
        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "select * from borrower where idCard = " + bor.getIdCard() + "; ";
            getBor = borDao.getBorInfoByOther(sql, conn);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return getBor;
    }




    @Override
    public Double borinfoSum() {
        Connection conn=null;
        Double sum=null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "select sum(price) sum from borinfo";
            sum = borDao.getBorInfoSum(sql, conn );
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return sum;

    }

    @Override
    public Double booksInfoSum() {
        Connection conn=null;
        Double bookPriceSum=null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "select sum(price) booksum  from books";
            bookPriceSum = borDao.getBooksPriceSum(sql, conn );
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return bookPriceSum;

    }

//    @Override
//    public boolean updateBorCancel(BorrowerBean bor) {
//
//
//
//        return false;
//    }


    @Override
    public boolean updateBorCancel(BorrowerBean bor) {
        boolean bool = false;
        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "UPDATE borrower SET isCancel = ? WHERE userId = ? ;";
            bool = borDao.updateBorCancel(sql, conn, bor);
            if (bool) {
                conn.commit();
            } else {
                conn.rollback();
            }
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return bool;
    }

    /**
     * 获得有与注销相关业务的用户信息
     * @return
     */
    @Override
    public List<BorrowerBean> getBorCancelInfo(BorrowerBean bor) {
        List<BorrowerBean> list = null;
        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "SELECT * FROM borrower WHERE isCancel ";
            if (bor.getIsCancel() == null) {
                sql += "!= '0';";
            } else {
                sql += "= '" + bor.getIsCancel() + "';";
            }
            list = borDao.getBorInfo(sql, conn);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    /**
     * 显示所有用户借书信息
     * @return
     */
    @Override
    public List<BorrowInfoBean> getBorrowInfoService(BorrowInfoBean bor) {
        List<BorrowInfoBean> list = null;
        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "SELECT borrower.username,x.bookname,x.bordate,x.repaydate,x.price,x.state" +
                    " from(SELECT borid,bookname,bordate,repaydate,borinfo.price price,borinfo.state state " +
                    "FROM borinfo,books WHERE borinfo.bookid=books.bookid)x,borrower " +
                    "WHERE borrower.userid=x.borid and borrower.username like '%" + bor.getUserName() + "%' and x.state like '%" + bor.getState() + "%'; ";
            list = borDao.getBorrowInfo(sql, conn);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

}
